package me.pearjelly.pmc.dao.impl;

import java.util.List;

import me.pearjelly.pmc.dao.DocDAO;
import me.pearjelly.pmc.util.Logger;

import org.apache.lucene.document.Document;
import org.springframework.jdbc.core.JdbcTemplate;

public class DocJDBCDAO implements DocDAO {
	@Override
	public List<Document> selectNeedReIndex(int maxRows) {
		List<Document> list = jdbcTemplate.query(DOCUMENTSQL
				, new DocumentPreparedStatementSetter(0, maxRows, 1)
				, new DocumentMapper());
		log.info("Find %d documents.",list.size());
		return list;
	}
	
	@Override
	public List<Document> selectCouldIndex(int beginIndex, int limit) {
		List<Document> list = jdbcTemplate.query(DOCUMENTSQL
				, new DocumentPreparedStatementSetter(beginIndex, limit, 0)
				, new DocumentMapper());
		log.info("Find %d documents.",list.size());
		return list;
	}
	
	@Override
	public int selectCouldIndexCount() {
		return jdbcTemplate.queryForInt("select count(*) from PICGROUPS where INDEXFRAG > 0");
	}

	private static final String TMP0SQL = new StringBuilder()
										.append("(select ")
										.append("	ID ")
										.append("	,PICGROUP_NAME ")
										.append("	,CATE_ID ")
										.append("	,PICGROUP_DESC ")
										.append("	,PICGROUP_PRIORITY ")
										.append("	,PICGROUP_MTIME ")
										.append("from PICGROUPS ")
										.append("where ID > ")
										.append("	(select ID ")
										.append("	from PICGROUPS ")
										.append("	where INDEXFRAG > ? ") //indexflag 0:never index,1:be indexed,2:need reindex
										.append("	limit ?,1 ) ")//beginIndex
										.append("and INDEXFRAG > ? ")
										.append("limit ? ) ")//size
										.append("as tmp0 ").toString();
	private static final String TMP1SQL = new StringBuilder()
										.append("(select ")
										.append("	C1.ID ")
										.append("	,C1.CATE_RELA ")
										.append("	,C2.CATE_DOMAIN ")
										.append("from CATEGORYS C1 ")
										.append("inner join CATEGORYS C2 ")
										.append("on C2.CATE_DOMAIN IS NOT NULL ")
										.append("and INSTR(C1.CATE_RELA,C2.ID) > 0 ")
										.append(") as tmp1 ").toString();
	private static final String TMP2SQL = new StringBuilder()
										.append("(select ")
										.append("	COUNT(ID) PICCOUNT ")
										.append("	,IF(PIC_TYPE=1,ID,MAX(ID)) FIRSTPIC_ID ")
										.append("	,PICGROUP_ID ")
										.append("	,EXTENSION FIRSTPIC_EXT ")
										.append("from PICS ")
										.append("group by PICGROUP_ID ")
										.append(") as tmp2 ").toString();
	private static final String DOCUMENTSQL = new StringBuilder()
										.append("select tmp0.ID ")
										.append("	,tmp0.PICGROUP_NAME ")
										.append("	,tmp0.CATE_ID ")
										.append("	,tmp0.PICGROUP_DESC ")
										.append("	,tmp0.PICGROUP_PRIORITY ")
										.append("	,tmp0.PICGROUP_MTIME ")
										.append("	,tmp1.CATE_RELA ")
										.append("	,tmp1.CATE_DOMAIN ")
										.append("	,tmp2.PICCOUNT ")
										.append("	,tmp2.FIRSTPIC_ID ")
										.append("	,tmp2.FIRSTPIC_EXT ")
										.append("from ")
										.append(TMP0SQL)
										.append("inner join ")
										.append(TMP1SQL)
										.append("on tmp0.CATE_ID = tmp1.ID ")
										.append("inner join ")
										.append(TMP2SQL)
										.append("on tmp0.ID = tmp2.PICGROUP_ID ")
										.toString();
	private static final Logger log = new Logger();
	JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}
